package com.example.demo.repository;

import com.example.demo.bean.TimetableBean;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.Map;

/**
 * @author zhangbiao
 * @date 2020/6/25 11:00
 * @desc 描述
 */
public interface TimetableRepository extends JpaRepository<TimetableBean, String> {

    public void deleteByObjId(String obj_id);

    @Query(value = "SELECT * FROM t_timetable WHERE class_name = ?1", nativeQuery = true)
    public Page<Map<String, Object>> findAllTimeableList(String class_name,Pageable pageable);

    @Query(value = "SELECT t.obj_id, t.class_name, t.course_name, t.week, t.start_node, t.end_node, u.nickname, u.account FROM t_timetable t, t_usertimetable_zj zj, t_user u where t.obj_id=zj.timetable_id and zj.user_id=u.obj_id and zj.user_id=?1",
            countQuery = "select count(*) FROM (SELECT t.obj_id, t.class_name, t.course_name, t.week, t.start_node, t.end_node, u.nickname, u.account FROM t_timetable t, t_usertimetable_zj zj, t_user u where t.obj_id=zj.timetable_id and zj.user_id=u.obj_id and zj.user_id=?1) o",
            nativeQuery = true)
    public Page<Map<String, Object>> findTimeableByUserList(Pageable pageable, String userId);


    //https://blog.csdn.net/linzhiqiang0316/article/details/79177077  JPA查询大全

}
